﻿namespace CRM.DataAccess.DAOs
{
    using System.Collections.Generic;
    using System.Data.Entity;
    using CRM.DataAccess.Entities;
    using System.Linq;
    using System;

    public class InvoiceDAO : DbContext
    {
        private LoyaltyDAO loyaltyDAO;
        private ItemDAO itemDAO;
        private ProductDAO proDAO;
        public InvoiceDAO()
            : base("name=Entities")
        {
            loyaltyDAO = new LoyaltyDAO();
            itemDAO = new ItemDAO();
            proDAO = new ProductDAO();
        }
        public DbSet<INVOICE_STATUS> INVOICE_STATUS { get; set; }
        public DbSet<INVOICE> INVOICE { get; set; }
        public DbSet<INVOICE_DETAIL> INVOICE_DETAIL { get; set; }
        public DbSet<ITEM> ITEMS { get; set; }
        public DbSet<ITEM_DETAIL> ITEM_DETAILS { get; set; }
        public DbSet<CONTACT> CONTACTS { get; set; }
        public DbSet<PRODUCT> PRODUCTS { get; set; }

        // get INVOICE_STATUS by ID
        public INVOICE_STATUS GetInvoiceStatusByID(long? id)
        {
            try
            {
                var Query = (from s in INVOICE_STATUS
                            where s.ID == id
                            select s);
                 List<INVOICE_STATUS> iis = Query.ToList();
                if (iis != null)
                    return iis[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //get  Invoice by ID
        public INVOICE GetInvoiceByID(long? ID)
        {
            try
            {
                if (ID != null)
                {
                    var Query = (from q in INVOICE
                                 where q.ID == ID
                                 select q).FirstOrDefault<INVOICE>();
                    INVOICE inv = Query as INVOICE;
                    return inv;
                }
                return null;
            }
            catch
            {
                return null;
            }
        }
        //get all Invoice Status
        public List<INVOICE_STATUS> GetAllInvoiceStatus()
        {
            try
            {
                var Query = from q in INVOICE_STATUS
                            select q;
                List<INVOICE_STATUS> ivss = Query.ToList();
                if (ivss.Count > 0)
                    return ivss;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Update Invoice
        public int UpdateInvoice(INVOICE invoice, ITEM item, List<ITEM_DETAIL> itemDetails)
        {
            var trans = this.Database.BeginTransaction();
            try
            {
                var original1 = ITEMS.Find(item.ID);
                if (original1 != null)
                {
                    Entry(original1).CurrentValues.SetValues(item);
                }
                else
                    ITEMS.Add(item);
                SaveChanges();
                var deleteQuery = from i in ITEM_DETAILS
                                  where i.ITEM_ID == item.ID
                                  select i;
                List<ITEM_DETAIL> its = deleteQuery.ToList();
                ITEM_DETAILS.RemoveRange(deleteQuery.ToList());
                int size1 = its.Count;
                double[,] arr1 = new double[2, size1];
                for (int i = 0; i < size1; i++)
                {
                    arr1[0, i] = its[i].PRODUCT_ID;
                    arr1[1, i] = Convert.ToDouble(its[i].QUANTITY.ToString());
                }
                for (int i = 0; i < size1; i++)
                {
                    for (int j = i + 1; j < size1; j++)
                    {
                        if (arr1[0, i] == arr1[0, j])
                        {
                            arr1[1, i] += arr1[1, j];
                            arr1[1, j] = 0;
                        }
                    }
                }
                int size2 = itemDetails.Count;
                double[,] arr2 = new double[2, size2];
                for (int i = 0; i < size2; i++)
                {
                    arr2[0, i] = itemDetails[i].PRODUCT_ID;
                    arr2[1, i] = Convert.ToDouble(itemDetails[i].QUANTITY.ToString());
                }
                for (int i = 0; i < size2; i++)
                {
                    for (int j = i + 1; j < size2; j++)
                    {
                        if (arr2[0, i] == arr2[0, j])
                        {
                            arr2[1, i] += arr2[1, j];
                            arr2[1, j] = 0;
                        }
                    }
                }

                for (int i = 0; i < size2; i++)
                {
                    if (arr2[1, i] != 0)
                    {
                        for (int j = 0; j < size1; j++)
                        {
                            if (arr1[0, j] == arr2[0, i] && arr1[1, j] != 0)
                            {
                                arr2[1, i] -= arr1[1, j];
                                break;
                            }
                        }
                        var original2 = PRODUCTS.Find((long)arr2[0, i]);
                        if (original2 != null)
                        {
                            PRODUCT pro = proDAO.GetProductByID((long)arr2[0, i]);
                            pro.BALANCE = pro.BALANCE - arr2[1, i];
                            pro.QTY_OUT_STOCK = pro.QTY_OUT_STOCK + arr2[1, i];
                            Entry(original2).CurrentValues.SetValues(pro);
                            SaveChanges();
                        }
                    }
                }
                foreach (ITEM_DETAIL i in itemDetails)
                {
                    i.ITEM_ID = item.ID;
                    this.ITEM_DETAILS.Add(i);
                }
                this.SaveChanges();
                var original3 = INVOICE.Find(invoice.ID);
                if (original3 != null)
                {
                    invoice.ITEM_ID = item.ID;
                    Entry(original3).CurrentValues.SetValues(invoice);
                    if (invoice.CONTACT_ID != null)
                    {
                        CONTACT contact = CONTACTS.Where(x => x.ID == invoice.CONTACT_ID).FirstOrDefault<CONTACT>();
                        contact = getContactLoyaltyPoint(contact, invoice, item);
                        this.SaveChanges();
                    }
                    SaveChanges();
                }
                trans.Commit();
                return 1;
            }
            catch
            {
                trans.Rollback();
                return -1;
            }
        }

        //Get Invoice by CONTACT_ID
        public List<INVOICE_DETAIL> GetInvoiceDetailByContactID(long contactid, long? assignedToID)
        {
            try
            {
                var Query = from i in INVOICE_DETAIL
                            where i.CONTACT_ID == contactid
                                && i.ASSIGN_TO_ID == (assignedToID == null ? i.InvoiceID : assignedToID)
                            select i;
                List<INVOICE_DETAIL> invs = Query.AsNoTracking().ToList();
                if (invs != null)
                    return invs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        
        //Get Invoice Detail by ID
        public INVOICE_DETAIL GetInvoiceDetailByID(long id, long? assignedToID)
        {
            try
            {
                var Query = from i in INVOICE_DETAIL
                            where i.InvoiceID == id
                                && i.ASSIGN_TO_ID == (assignedToID == null ? i.ASSIGN_TO_ID : assignedToID)
                            select i;
                List<INVOICE_DETAIL> invs = Query.AsNoTracking().ToList();
                if (invs != null)
                    return invs[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get Invoice by condition
        public List<INVOICE_DETAIL> GetInvoiceByCondition(DateTime? dateFrom, DateTime? dateTo, long? status_ID, List<long?> ListConID, List<long?> ListOrgID, long? assignUser_ID)
        {
            try
            {
                List<INVOICE_DETAIL> sum = new List<INVOICE_DETAIL>();
                if (ListOrgID.Count > 0)
                {
                    if (ListConID.Count > 0)
                    {
                        foreach (long? OrgID in ListOrgID)
                        {
                            foreach (long? ContactID in ListConID)
                            {
                                var Query1 = from q in INVOICE_DETAIL
                                             where
                                                  q.STATUS_ID == (status_ID == null ? q.STATUS_ID : status_ID)
                                                && q.CONTACT_ID == (ContactID == null ? q.CONTACT_ID : ContactID)
                                                && q.OrgID == (OrgID == null ? q.OrgID : OrgID)
                                                && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                                 && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                             select q;
                                List<INVOICE_DETAIL> invd = Query1.AsNoTracking().ToList();
                                sum.AddRange(invd);
                            }
                        }
                    }
                    else
                    {
                        foreach (long? OrgID in ListOrgID)
                        {
                            var Query1 = from q in INVOICE_DETAIL
                                         where
                                              q.STATUS_ID == (status_ID == null ? q.STATUS_ID : status_ID)
                                            && q.OrgID == (OrgID == null ? q.OrgID : OrgID)
                                            && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                             && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                         select q;
                            List<INVOICE_DETAIL> invd = Query1.AsNoTracking().ToList();
                            sum.AddRange(invd);
                        }
                    }

                }
                else
                {
                    if (ListConID.Count > 0)
                    {
                        foreach (long? ContactID in ListConID)
                        {
                            var Query1 = from q in INVOICE_DETAIL
                                         where
                                              q.STATUS_ID == (status_ID == null ? q.STATUS_ID : status_ID)
                                            && q.CONTACT_ID == (ContactID == null ? q.CONTACT_ID : ContactID)
                                            && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                             && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                         select q;
                            List<INVOICE_DETAIL> invd = Query1.AsNoTracking().ToList();
                            sum.AddRange(invd);
                        }
                    }
                    else
                    {
                        var Query1 = from q in INVOICE_DETAIL
                                     where
                                          q.STATUS_ID == (status_ID == null ? q.STATUS_ID : status_ID)
                                        && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                         && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                     select q;
                        List<INVOICE_DETAIL> invd = Query1.AsNoTracking().ToList();
                        sum.AddRange(invd);
                    }
                }

                if (sum.Count > 0)
                    return sum;
                return null;
            }
            catch
            {
                return null;
            }
        }

        // add new Invoice
        public int AddNewInvoice(INVOICE invoice, ITEM item, List<ITEM_DETAIL> itemDetails)
        {
            var trans = this.Database.BeginTransaction();
            try
            {
                this.ITEMS.Add(item);
                this.SaveChanges();
                int size = itemDetails.Count;
                double[,] arr = new double[2, size];
                for (int i = 0; i < size; i++)
                {
                    arr[0, i] = itemDetails[i].PRODUCT_ID;
                    arr[1, i] = Convert.ToDouble(itemDetails[i].QUANTITY.ToString());
                }
                for (int i = 0; i < size; i++)
                {
                    for (int j = i + 1; j < size; j++)
                    {
                        if (arr[0, i] == arr[0, j])
                        {
                            arr[1, i] += arr[1, j];
                            arr[1, j] = 0;
                        }
                    }
                }

                for (int i = 0; i < size; i++)
                {
                    if (arr[1, i] != 0)
                    {
                        var original1 = PRODUCTS.Find((long)arr[0, i]);
                        if (original1 != null)
                        {
                            PRODUCT pro = proDAO.GetProductByID((long)arr[0, i]);
                            pro.BALANCE -= arr[1, i];
                            pro.QTY_OUT_STOCK += arr[1, i];
                            Entry(original1).CurrentValues.SetValues(pro);
                            SaveChanges();
                        }
                    }
                }

                foreach (ITEM_DETAIL idetail in itemDetails)
                {

                    idetail.ITEM_ID = item.ID;
                    this.ITEM_DETAILS.Add(idetail);
                    this.SaveChanges();
                }
                invoice.ITEM_ID = item.ID;
                this.INVOICE.Add(invoice);
                this.SaveChanges();
                if (invoice.CONTACT_ID != null)
                {
                    CONTACT contact = CONTACTS.Where(x => x.ID == invoice.CONTACT_ID).FirstOrDefault<CONTACT>();
                    contact = getContactLoyaltyPoint(contact, invoice, item);
                    this.SaveChanges();
                }
                trans.Commit();
                return 1;
            }
            catch
            {
                trans.Rollback();
                return -1;
            }
        }

        public CONTACT getContactLoyaltyPoint(CONTACT contact, INVOICE invoice, ITEM item)
        {
            if (contact != null)
            {
                double currPoint = 0;
                double totalPrice = item.TOTAL_PRICE == null ? 0 : (double)item.TOTAL_PRICE;
                LOYALTY currLoyalty = loyaltyDAO.GetLoyaltyByID((long)(contact.LOYALTY_STARTER_ID), invoice.ASSIGNED_TO);
                if (currLoyalty != null)
                {
                    LOYALTY nextLoyalty = currLoyalty;
                    var allInvsQuery = from inv in INVOICE_DETAIL
                                       where inv.InvoiceID != invoice.ID && inv.ASSIGN_TO_ID == contact.ASSIGNED_USER_ID
                                       select inv;
                    List<INVOICE_DETAIL> allInvs = allInvsQuery.AsNoTracking().ToList();
                    foreach (INVOICE_DETAIL inv in allInvs)
                    {
                        totalPrice += (inv.TOTAL_PRICE == null ? 0 : (double)inv.TOTAL_PRICE);
                    }
                    currPoint = totalPrice * (double)currLoyalty.EXCHANGE_RATE;
                    List<LOYALTY> nextLoyalties = loyaltyDAO.GetNextLoyaltyByPointsToAward(currLoyalty.POINTS_TO_AWARD, invoice.ASSIGNED_TO);
                    foreach (LOYALTY loy in nextLoyalties)
                    {
                        if (currPoint > loy.POINTS_TO_AWARD)
                        {
                            currLoyalty = nextLoyalty;
                            nextLoyalty = loy;
                            //lifetimePoint += (double)loy.POINTS_TO_AWARD;
                            totalPrice -= (double)currLoyalty.POINTS_TO_AWARD / (double)currLoyalty.EXCHANGE_RATE * 1.0;
                            if (totalPrice > 0)
                                currPoint = totalPrice * (double)nextLoyalty.EXCHANGE_RATE;
                        }
                    }

                    contact.LOYALTY_ID = nextLoyalty.ID;
                    contact.LOYALTY_NAME = nextLoyalty.NAME;
                    contact.POINTS = currPoint;
                }
            }

            return contact;
        }
        //Delete Invoice
        public int DeleteInvoice(long invoiceID)
        {
            var trans = this.Database.BeginTransaction();
            try
            {
                INVOICE invoice = INVOICE.Where(x => x.ID == invoiceID).FirstOrDefault<INVOICE>();
                ITEM item = itemDAO.GetItemByID(invoice.ITEM_ID);
                INVOICE.RemoveRange(INVOICE.Where(x => x.ID == invoiceID));
                this.SaveChanges();
                if (invoice.CONTACT_ID != null)
                {
                    item.TOTAL_PRICE = 0;
                    CONTACT contact = CONTACTS.Where(x => x.ID == invoice.CONTACT_ID).FirstOrDefault<CONTACT>();
                    contact = getContactLoyaltyPoint(contact, invoice, item);
                    this.SaveChanges();
                }
                trans.Commit();
                return 1;
            }
            catch
            {
                trans.Rollback();
                return -1;
            }
        }
    }
}